package Passageiros;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Vo.AeronavesVO;

public class AeronavesDAO extends DataSourceDAO
{
	List<AeronavesVO> comboAeronaves = new ArrayList<AeronavesVO>();
	public AeronavesDAO( ) 
	{
	}
	
	//metodo responsavel por retornar todas as Aeronaves disponiveis no modelo fisico dados
	public List<AeronavesVO> listarCidades() throws DAOException
	{
		AeronavesVO aeronaves = null;
        String select = "SELECT * FROM CIDADES";
        PreparedStatement pstmt	= null;
        ResultSet rs 			= null;
        
        try
		{
			connect( ) ;
            pstmt = connection.prepareStatement( select );
            //pstmt.setInt( 1, codigo ) ; // Narcisio, verificar esta linha
            rs = pstmt.executeQuery( ) ;
            
            rs.beforeFirst();
        
            
            while (rs.last() != false)
            {
            	String tipoAeronave = rs.getString("nome");
            	aeronaves = new AeronavesVO( );
            	aeronaves.setTipo(tipoAeronave);
                    
            	aeronaves.setCodigo(rs.getInt("codigo"));
                    
            	comboAeronaves.add(aeronaves);              
            }
		}
		catch ( SQLException e ) 
		{
			e.printStackTrace();
			throw new DAOException("Impossivel consultar cidades!", e );
		}
		finally 
		{
			try 
			{
				if ( pstmt != null ) 
                {
                    pstmt.close();
                }
                if ( rs != null ) 
                {
                    rs.close();
                }
                close( ) ;
            } 
            catch ( SQLException e ) 
            {
                e.printStackTrace( ) ;
            }
        }
        return comboAeronaves ;        
	}

    public AeronavesVO consultarAeronaves( int codigo ) throws DAOException  
    {
    	AeronavesVO aeronaves = null;
        String select = "SELECT * FROM AERONAVES WHERE CODIGO = ?";
        PreparedStatement pstmt	= null;
        ResultSet rs 			= null;
        
        try 
        {
        	connect( ) ;
            pstmt = connection.prepareStatement( select );
            pstmt.setInt( 1, codigo ) ;
            rs = pstmt.executeQuery( ) ;
            if ( rs.next() ) 
            {
                String tipo     = rs.getString("TIPO");
            
                aeronaves = new AeronavesVO( );
                aeronaves.setCodigo( codigo );
                aeronaves.setTipo( tipo );
                
            }
        } 
        catch ( SQLException e ) 
        {
            e.printStackTrace();
            throw new DAOException("Impossivel consultar aeronaves!", e );
        } 
        finally 
        {
            try 
            {
                if ( pstmt != null ) 
                {
                    pstmt.close();
                }
                if ( rs != null ) 
                {
                    rs.close();
                }
                close( ) ;
            } 
            catch ( SQLException e ) 
            {
                e.printStackTrace( ) ;
            }
        }
        return aeronaves ;
    }
    
	public void inserirAeronaves( AeronavesVO aeronaves ) throws DAOException  
    {
        String insert = "INSERT INTO AERONAVES ( CODIGO, TIPO) VALUES (?,?)";
        PreparedStatement pstmt = null;
        
        try 
        {
        	connect( ) ;
            connection.setAutoCommit( false );
            pstmt = connection.prepareStatement( insert );
            pstmt.setInt(1, aeronaves.getCodigo());
            pstmt.setString(2, aeronaves.getTipo());
            pstmt.executeUpdate( ) ;
            commit( ) ;
        } 
        catch ( SQLException e ) 
        {
            rollback( ) ;
            e.printStackTrace( ) ;
            throw new DAOException ( "Impossivel inserir aeronaves!" , e ) ;
        } 
        finally 
        {
            try 
            {
                if ( pstmt != null ) 
                {
                    pstmt.close();
                }
                close( ) ;
            } 
            catch ( SQLException e ) 
            {
                e.printStackTrace( );
            }
        }
    }
    
    public void excluirAeronaves( AeronavesVO aeronaves )  throws DAOException  
    {
        String delete 			= "DELETE FROM AERONAVES WHERE CODIGO = ?";
        PreparedStatement pstmt = null;
        
        try 
        {
        	connect( ) ;
            connection.setAutoCommit( false );
            pstmt = connection.prepareStatement( delete );
            pstmt.setInt(1, aeronaves.getCodigo( ) ) ;
            pstmt.executeUpdate( ) ;
            commit( ) ;
        } 
        catch ( SQLException e ) 
        {
            rollback( ) ;
            e.printStackTrace( ) ;
            throw new DAOException ( "Impossivel deletar Aeronave!" , e ) ;
        } 
        finally 
        {
            try 
            {
                if ( pstmt != null ) 
                {
                    pstmt.close();
                }
                close( ) ;
            } 
            catch ( SQLException e ) 
            {
                e.printStackTrace( ) ;
            }
        }
    }
    
    public void alterarAeronaves( AeronavesVO aeronaves ) throws DAOException  
    {
        String insert = "UPDATE AERONAVES SET TIPO = ? WHERE CODIGO = ?";
        PreparedStatement pstmt = null;
        
        try 
        {
        	connect( ) ;
            connection.setAutoCommit( false );
            pstmt = connection.prepareStatement( insert);
            pstmt.setString( 1, aeronaves.getTipo());
            pstmt.setInt(2, aeronaves.getCodigo());
            pstmt.executeUpdate ();
            commit( ) ;
        } 
        catch ( SQLException e ) 
        {
            rollback( ) ;
            e.printStackTrace( ) ;
            throw new DAOException ( "Impossivel alterar aeronaves!" , e ) ;
        } 
        finally 
        {
            try 
            {
                if ( pstmt != null ) 
                {
                    pstmt.close();
                }
                close( ) ;
            } 
            catch ( SQLException e ) 
            {
                e.printStackTrace();
            }
        }
    }
}
